package com.os.core.service.dataopt.impl;

import com.os.common.annotation.ds.DynamicDatasource;
import com.os.common.entity.extend.ParentChildMatrix;
import com.os.common.entity.field.FieldMerge;
import com.os.common.entity.field.FieldNameAlter;
import com.os.common.entity.foreignkey.RefTableNameAndColumnName;
import com.os.common.exception.ErrorException;
import com.os.common.exception.NoContentException;
import com.os.common.utils.MyUtil;
import com.os.core.mapper.DataoptMapper;
import com.os.core.service.dataopt.DataoptService;
import org.springframework.stereotype.Service;

import javax.naming.NotContextException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 描述：数据操作实现类
 *
 * @author huxuehao
 **/
@Service
public class DataoptServiceImpl implements DataoptService {

    private final DataoptMapper dataoptMapper;

    public DataoptServiceImpl(DataoptMapper dataoptMapper) {
        this.dataoptMapper = dataoptMapper;
    }

    @Override
    public List<String> deleteTableDataOfSimple(List<String> tableNames, String filterSql) {
        List<String> list = new LinkedList<>();
        for (String tableName : tableNames) {
            list.add("DELETE FROM `".concat(tableName).concat("`").concat(MyUtil.isEmpty(filterSql) ? "" : " ".concat(filterSql)));
        }
        if (MyUtil.isEmpty(list)) {
            throw new NoContentException("抱歉，没有任何内容");
        }
        return list.stream().map(item -> item.concat(";")).collect(Collectors.toList());
    }

    @Override
    @DynamicDatasource
    public List<String> deleteTableDataOfPrimary(String datasourceId, List<String> tableNames, String filterSql) {
        if (dataoptMapper.containView(tableNames).size() > 0) {
            throw new ErrorException("操作表中不允许包括视图");
        }
        List<String> list = new LinkedList<>();
        List<Map<String, String>> primary = dataoptMapper.getPrimary(tableNames);
        List<Map<String, String>> foreignKeyInfo = dataoptMapper.getForeignKeyInfo(tableNames);
        for (String tableName : tableNames) {
            String deletePrimarySql = "DELETE FROM `" .concat(tableName).concat("`").concat(MyUtil.isEmpty(filterSql) ? "" : " ".concat(filterSql));
            for (Map<String, String> map : primary) {
                String mapKey = map.get("TABLE_NAME").concat("::").concat(map.get("COLUMN_NAME"));
                if (mapKey.startsWith(tableName)) {
                    List<Map<String, String>> collect = foreignKeyInfo.stream().filter(item -> mapKey.equals(item.get("SOURCE"))).collect(Collectors.toList());
                    for (Map<String, String> targetMap : collect) {
                        String[] split = targetMap.get("TARGET").split("::");
                        if (split.length == 2) {
                            String deleteForeignSql = "DELETE FROM `".concat(split[0])
                                    .concat("` WHERE `")
                                    .concat(split[1])
                                    .concat("` IN (")
                                    .concat(deletePrimarySql.replaceFirst("DELETE","SELECT `".concat(map.get("COLUMN_NAME")).concat("`")))
                                    .concat(")");
                            list.add(deleteForeignSql);
                        }
                    }
                }
            }
            list.add(deletePrimarySql);
        }
        return list.stream().map(item -> item.concat(";")).collect(Collectors.toList());
    }

    @Override
    public String alertFieldValue(FieldNameAlter fieldNameAlter){
        StringBuilder updateSql = new StringBuilder("UPDATE `".concat(fieldNameAlter.getTableName()).concat("` SET "));
        if ("insertPrefix".equalsIgnoreCase(fieldNameAlter.getRule())) {
            for (String columnName : fieldNameAlter.getFieldNames()) {
                updateSql.append("`".concat(columnName).concat("`=CONCAT('").concat(fieldNameAlter.getSubFiledValue()).concat("',`").concat(columnName).concat("`),"));
            }
        } else {
            for (String columnName : fieldNameAlter.getFieldNames()) {
                updateSql.append("`".concat(columnName).concat("`=CONCAT(`").concat(columnName).concat("`,'").concat(fieldNameAlter.getSubFiledValue()).concat("'),"));
            }
        }
        updateSql = new StringBuilder(updateSql.substring(0, updateSql.length() - 1) + ";");
        return updateSql.toString();
    }

    @Override
    public String mergeFields(FieldMerge fieldMerge) {
        String updateSql = "";
        if ("mergeNew".equalsIgnoreCase(fieldMerge.getRule())) {
            updateSql += "ALTER TABLE `"
                    .concat(fieldMerge.getTableName())
                    .concat("` ADD COLUMN `")
                    .concat(fieldMerge.getMergeFieldTo())
                    .concat("` ")
                    .concat(fieldMerge.getMergeFieldType())
                    .concat(";\n\n");
        }
        updateSql += "UPDATE `".concat(fieldMerge.getTableName()).concat("` SET `").concat(fieldMerge.getMergeFieldTo()).concat("` = CONCAT(");
        List<String> fieldNames = fieldMerge.getFieldNames();
        String collect = fieldNames.stream().map(item -> "`" + item + "`").collect(Collectors.joining(",".concat("'").concat(fieldMerge.getRegx()).concat("',")));
        updateSql += collect.concat(");");
        return updateSql;
    }

    @Override
    public String parentChildMatrix(ParentChildMatrix matrix) {
        // 获取表名
        String tableName = matrix.getTableName();
        // 获取父键字段名
        String parentField = matrix.getParentFieldName();
        // 获取子键字段名
        String childField = matrix.getChildFieldName();
        //矩阵展示时的字段名
        String showField = matrix.getShowFieldName();
        // 获取过滤SQL
        String filterSQL = matrix.getFilterSQL();
        // 去除最后的“;”
        if (filterSQL != null && filterSQL.equals((filterSQL.substring(0,filterSQL.length()-1))+";")) {
            filterSQL = filterSQL.substring(0,filterSQL.length()-1);
        }
        // 设置别名时使用（int 转 char 再转 String）
        int number = 97;
        // 拼接SQL
        StringBuilder selectSql = new StringBuilder("SELECT");
        StringBuilder fromAndJoinSql = new StringBuilder("\nFROM ");
        String whereSql = "\nWHERE "+
                (char) number + ".`" + showField + "` IN " +
                "( SELECT `" + showField + "` FROM `"+tableName+"` "+
                "WHERE `"+childField+"` NOT IN (SELECT `"+parentField+"` FROM `"+tableName+"`) OR `"+childField+"` IS NULL)";
        StringBuilder orderBySql = new StringBuilder("\nORDER BY");
        for (int i = 0; i < matrix.getMatrixLevel(); i++) {
            String alias = String.valueOf((char) number);
            if (i+1 == matrix.getMatrixLevel()) {
                selectSql.append("\n").append(alias).append(".`").append(showField).append("` level").append(i + 1);
                orderBySql.append("\n").append(alias).append(".`").append(showField).append("`;");
            } else {
                selectSql.append("\n").append(alias).append(".`").append(showField).append("` level").append(i + 1).append(",");
                orderBySql.append("\n").append(alias).append(".`").append(showField).append("`,");
            }

            if (i == 0) {
                fromAndJoinSql.append("(").append(filterSQL).append(") ").append(alias);
            } else {
                fromAndJoinSql.append("\nLEFT JOIN (").append(filterSQL).append(") ").append(alias);
                fromAndJoinSql.append("\nON ").append(((char) (number - 1))).append(".`").append(parentField).append("` = ").append(alias).append(".`").append(childField).append("`");
            }
            number++;
        }
        return selectSql + fromAndJoinSql.toString() + whereSql + orderBySql;
    }

    @Override
    @DynamicDatasource
    public List<RefTableNameAndColumnName> foreignKeyOfPrimaryKey(String datasourceId, String tableName) throws NotContextException {
        List<RefTableNameAndColumnName> list = new LinkedList<>(); // 存放逻辑外键的主外表字段关系
        List<Map<String, String>> primary = dataoptMapper.getPrimary(Collections.singletonList(tableName));
        if (MyUtil.isEmpty(primary)) {
            throw new NotContextException("抱歉,"+tableName+"表中不存在主键");
        }
        List<String> targetTableNames = dataoptMapper.getTableNames();
        for (String targetTableName : targetTableNames) {
            Map<String,Integer> targetColumnMap = new HashMap<>();

            List<String> targetColumns = dataoptMapper.getColumns(targetTableName);
            List<Map<String, String>> foreignKey = dataoptMapper.foreignKeyOfPrimaryKey(targetTableName, targetColumns, tableName, primary.get(0).get("COLUMN_NAME"));
            if (MyUtil.isEmpty(foreignKey)) {
                continue;
            }
            for (Map<String, String> map : foreignKey) {
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    if ("1".equals(entry.getValue())){
                        if (targetColumnMap.containsKey(entry.getKey())) {
                            targetColumnMap.put(entry.getKey(),targetColumnMap.get(entry.getKey()) + 1);
                        } else {
                            targetColumnMap.put(entry.getKey(),1);
                        }
                    }
                }
            }
            if (MyUtil.isEmpty(targetColumnMap)) {
                continue;
            }
            for (Map.Entry<String, Integer> entry : targetColumnMap.entrySet()) {
                if (entry.getValue() == foreignKey.size()) {
                    list.add(new RefTableNameAndColumnName(tableName,primary.get(0).get("COLUMN_NAME"),targetTableName,entry.getKey()));
                }
            }
        }
        if (MyUtil.isEmpty(list)) {
            throw new NotContextException("抱歉,"+tableName+"表未找到外键表");
        }
        return list;
    }
}
